package com.jinbai.repo;

import com.jinbai.data_object.TrackDO;
import com.jinbai.util.DBUtil;
import com.jinbai.util.Log;
import lombok.SneakyThrows;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;

public class TrackRepo {
    @SneakyThrows
    public void insert(Integer uid, String title, String contentType, InputStream inputStream) {
        try (Connection con = DBUtil.connection()) {
            String sql = "insert into tracks (uid, type, title, content) values (?, ?, ?, ?)";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, uid);
                ps.setString(2, contentType);
                ps.setString(3, title);
                ps.setBinaryStream(4, inputStream);

                Log.println(ps.toString());
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByUidLimitOffset(Integer uid, int limit, int offset) {
        List<TrackDO> listTrackDOS = new LinkedList<>();
        try (Connection con = DBUtil.connection()) {
            String sql = "select tid, uid, title,type from tracks where uid = ? order by tid desc limit ? offset ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, uid);
                ps.setInt(2, limit);
                ps.setInt(3, offset);

                Log.println("执行SQL: " + ps);
                try (final ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        listTrackDOS.add(
                                new TrackDO(rs.getInt("tid"),
                                        rs.getInt("uid"),
                                        rs.getString("title"),
                                        rs.getString("type")));
                    }
                }
            }
        }
        return listTrackDOS;
    }

    @SneakyThrows
    public int selectCountByUid(Integer uid) {
        try (Connection con = DBUtil.connection()) {
            String sql = "select count(*) from tracks where uid = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, uid);
                Log.println("执行SQL：" + ps);
                try (final ResultSet rs = ps.executeQuery()) {
                    rs.next(); // 肯定只有一条
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByTidList(List<Integer> tidList) {
        final String s = String.join(", ", tidList.stream().map(String::valueOf).collect(Collectors.toList()));
        String sql = String.format("select tid, title, type from tracks where tid in (%s) order by tid", s);
        List<TrackDO> ans = new LinkedList<>();
        try (Connection con = DBUtil.connection()) {
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                Log.println("执行查询所有音频的语句为" + ps);
                try (final ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        ans.add(new TrackDO(rs.getInt("tid"),
                                rs.getString("title"),
                                rs.getString("type")));
                    }
                }
            }
        }
        return ans;
    }
}
